﻿using System;
using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;
using System.Text;

using com.vs.DataAccess;
using com.vs.DataAccess.DO;
using com.vs.General;

namespace com.vs.Inventory.BL
{
    public class InventoryManager
    {
        public DataTable GetInventoryByQFCri(Session sess, List<KeyValuePair<string, KeyValuePair<string, object>>> dcParam, string stOrderBy, ref string stError)
        {
            DataHelper helper = null;
            StringBuilder sbSQL = null;
            int i = 0;
            //IDataParameter[] pars = null;
            List<IDataParameter> pars = null;

            try
            {
                sbSQL = new StringBuilder(" SELECT L.ZONE, LI.INTERNAL_LOCATION_INV, L.WAREHOUSE, L.LOCATION, LI.ITEM, LI.ITEM_DESC, LI.COMPANY, LI.PERMANENT, LI.ATTRIBUTE_NUM, LI.ON_HAND_QTY, LI.IN_TRANSIT_QTY, LI.ALLOCATED_QTY, LI.QUANTITY_UM, LI.INVENTORY_STS, LI.AGING_DATE, LI.USER_STAMP, LI.DATE_TIME_STAMP, LI.LPN, LI.PARENT_LPN, LI.ATTRIBUTE1, LI.ATTRIBUTE2, LI.ATTRIBUTE3, LI.ATTRIBUTE4, LI.ATTRIBUTE5, LI.ATTRIBUTE6, LI.ATTRIBUTE7, LI.ATTRIBUTE8 ")
                    .Append(" FROM LOCATION L LEFT JOIN LOCATION_INVENTORY LI ON (LI.WAREHOUSE = L.WAREHOUSE AND LI.LOCATION = L.LOCATION) WHERE 1=1 ");
                helper = new DataHelper(sess);
                //pars = new IDataParameter[dcParam.Count];
                pars = new List<IDataParameter>();
                foreach (KeyValuePair<string, KeyValuePair<string, object>> de in dcParam)
                {
                    if (StringManager.Equals(de.Value.Key, " IS NOT NULL "))
                    {
                        sbSQL.Append(" AND ").Append(de.Key.ToString()).Append(de.Value.Key);
                    }
                    else
                    {
                        sbSQL.Append(" AND ").Append(de.Key.ToString()).Append(de.Value.Key).Append("@Value").Append(i);
                        //pars[i] = new SqlParameter("@Value" + i, de.Value.Value);
                        pars.Add(new SqlParameter("@Value" + i, de.Value.Value));
                        i++;
                    }
                }

                if (!StringManager.IsEmpty(stOrderBy))
                {
                    sbSQL.Append(stOrderBy);
                }
                if (pars.Count > 0)
                    return helper.GetTable(CommandType.Text, sbSQL.ToString(), pars.ToArray());
                else
                    return helper.GetTable(CommandType.Text, sbSQL.ToString());
            }
            catch (Exception e)
            {
                ExceptionManager.LogException(sess, e);
                stError = MessageManager.MSG_GEN_000 + e.Message;
                return null;
            }
            finally
            {
            }
        }

        public List<LocationInventoryDO> GetInventory(Session sess, string stWhs, string stComp, string stItem, string stLoc, string stLPN, int iAttrNum, string stInvSts)
        {
            List<KeyValuePair<string, KeyValuePair<string, object>>> dcCond = null;

            try
            {
                dcCond = new List<KeyValuePair<string, KeyValuePair<string, object>>>();
                if (!StringManager.IsEmpty(stWhs))
                    dcCond.Add(new KeyValuePair<string,KeyValuePair<string,object>>("WAREHOUSE", new KeyValuePair<string,object>("=", stWhs)));
                if (!StringManager.IsEmpty(stComp))
                    dcCond.Add(new KeyValuePair<string,KeyValuePair<string,object>>("COMPANY", new KeyValuePair<string,object>("=", stComp)));
                if (!StringManager.IsEmpty(stItem))
                    dcCond.Add(new KeyValuePair<string,KeyValuePair<string,object>>("ITEM", new KeyValuePair<string,object>("=", stItem)));
                if (!StringManager.IsEmpty(stLoc))
                    dcCond.Add(new KeyValuePair<string,KeyValuePair<string,object>>("LOCATION", new KeyValuePair<string,object>("=", stLoc)));
                if (!StringManager.IsEmpty(stLPN))
                    dcCond.Add(new KeyValuePair<string,KeyValuePair<string,object>>("LPN", new KeyValuePair<string,object>("=", stLPN)));
                if (iAttrNum > 0)
                    dcCond.Add(new KeyValuePair<string,KeyValuePair<string,object>>("ATTRIBUTE_NUM", new KeyValuePair<string,object>("=", iAttrNum)));
                if (!StringManager.IsEmpty(stInvSts))
                    dcCond.Add(new KeyValuePair<string,KeyValuePair<string,object>>("INVENTORY_STS", new KeyValuePair<string,object>("=", stInvSts)));

                return LocationInventoryDO.GetListByColumns(sess, dcCond, null);
            }
            catch (Exception e)
            {
                ExceptionManager.LogException(sess, e);
                return null;
            }
        }

        public string DeleteAllNegativeInv(Session sess)
        {
            DataHelper helper = null;

            try
            {
                helper = new DataHelper(sess);
                helper.Update(CommandType.Text, "delete from location_inventory where on_hand_qty < 0 or in_transit_qty< 0 or allocated_qty < 0");
                return null;
            }
            catch (Exception e)
            {
                ExceptionManager.LogException(sess, e);
                return MessageManager.MSG_GEN_000 + e.Message;
            }
            finally
            {
                if (helper != null)
                    helper.Dispose();
            }
        }
    }
}
